package cn.liip.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.tomcat.dbcp.dbcp2.BasicDataSource;

import cn.liip.beans.Student;

public class MySqlUtil {

	Connection conn = null;

	public Connection getConn() {
		return conn;
	}

	public MySqlUtil() {
		this("localhost", "struts_test", "root", "");
	}

	void initDataSource() {
		BasicDataSource ds = new BasicDataSource();

	}

	public MySqlUtil(String host, String database, String user, String pwd) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager
					.getConnection("jdbc:mysql://" + host + "/" + database + "?user=" + user + "&password=" + pwd);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}

	public int update(Student student) {
		int result = -1;
		String sql = " update students set address = ?, age = ?, name = ?, number = ?, sex = ? where _id = ? ";
		PreparedStatement stat = null;

		try {
			stat = conn.prepareStatement(sql);
			stat.setString(1, student.getAddress());
			stat.setInt(2, student.getAge());
			stat.setString(3, student.getName());
			stat.setString(4, student.getNumber());
			stat.setString(5, student.getSex());
			stat.setString(6, student.get_id());
			result = stat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;

	}

	public Student findOne(String _id) {
		PreparedStatement stat = null;
		ResultSet rs = null;
		Student student = new Student();
		try {
			String sql = "select * from students where _id = ? ";
			stat = conn.prepareStatement(sql);
			stat.setString(1, _id);
			rs = stat.executeQuery();
			if (rs.getFetchSize() > 1) {
				return null;
			}
			while (rs.next()) {
				student.set_id(rs.getString("_id"));
				student.setAddress(rs.getString("address"));
				student.setAge(Integer.parseInt(rs.getString("age")));
				student.setName(rs.getString("name"));
				student.setNumber(rs.getString("number"));
				student.setSex(rs.getString("sex"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return student;
	}

	public int deleteOne(String _id) {
		int result = -1;

		PreparedStatement stat = null;
		String sql = " delete from students where _id = ? ";
		try {
			stat = conn.prepareStatement(sql);
			stat.setString(1, _id);
			result = stat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public int addOne(Student student) {
		int result = -1;
		PreparedStatement stat = null;
		String sql = "insert into students (_id, address, age, name, number, sex) values (?, ?, ?, ?, ?, ?)";
		try {
			stat = conn.prepareStatement(sql);
			stat.setString(1, System.currentTimeMillis() + "");
			stat.setString(2, student.getAddress());
			stat.setInt(3, student.getAge());
			stat.setString(4, student.getName());
			stat.setString(5, student.getNumber());
			stat.setString(6, student.getSex());
			result = stat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public List<Student> findAll() {
		List<Student> students = new ArrayList<Student>();
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			String sql = "select * from students ";
			stat = conn.prepareStatement(sql);
			rs = stat.executeQuery();
			while (rs.next()) {
				Student student = new Student();
				student.set_id(rs.getString("_id"));
				student.setAddress(rs.getString("address"));
				student.setAge(Integer.parseInt(rs.getString("age")));
				student.setName(rs.getString("name"));
				student.setNumber(rs.getString("number"));
				student.setSex(rs.getString("sex"));
				students.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return students;
	}

}
